<%-- 
    Document   : index
    Created on : Sep 10, 2011, 10:07:08 PM
    Author     : Lavani
--%>

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@page import="java.sql.*" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
    "http://www.w3.org/TR/html4/loose.dtd">

<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>Availability Check</title>

        <style>
            h2 {color:white; font-family:"Times New Roman";}
            td {color:"#003471"; font-family:"Times New Roman";}
        </style>

        <link rel="stylesheet" href='style.css' type="text/css" />




    </head>
    <body >
        <table width="974" border="0" style="position:relative; top:-17px; margin-left:auto; margin-right:auto;" cellspacing="10">
            <tr>
                <td background="images/bgg.jpg" height="130px" width="90px" colspan="5" align="center">
                </td>
            </tr>

            <tr>
                <td align="center" colspan="5"> 
                    <div id="navigation">
                        <ul>
                            <li><a href="index.jsp">Home</a></li>
                            <li><a href="Login.jsp">Sign In</a></li>
                            <li><a href="Registration.jsp">Sign Up</a></li>
                            <li><a href="navigation.html">Contact Us</a></li>
                        </ul>
                    </div>    
                </td>
            </tr>
            <tr>
                <td id="k">
                    Welcome &nbsp; <font color="#889022"><%= session.getAttribute("l")%></font> 
                </td>
                <td>
                    <a href="UserProfile.jsp">My Home</a>
                </td>
                <td>
                   <font color="black"> Cancel Booking </font>
                </td>
                <td>
                    <font color="black"> Change Password</font>
                </td>
                <td id="k"> <a href="Login3.jsp" >Logout </a>
                </td>
                <td></td>
            </tr>
            <tr>
                <td colspan="5">
                    <form name="avail" method="POST" action="Booking.jsp">
                        <input type="hidden" name="action" value="avail">
                        <input type="hidden" name="hide" value="">
                    <br><br>
                    <%
                        Connection con = null;
                        ResultSet rs1 = null, rs2 = null, rs3 = null;
                        Statement st1 = null, st2 = null, st3 = null;
                        int seatsLeft = 0;
                        //try {
                        String src = request.getParameter("Source");
                        String des = request.getParameter("Destination");
                        String clss = request.getParameter("Class");
                        String date = request.getParameter("datum1");

                        Class.forName("oracle.jdbc.driver.OracleDriver");
                        con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "system", "admin");

                        st1 = con.createStatement();
                        st2 = con.createStatement();
                        st3 = con.createStatement();
                        rs1 = st1.executeQuery("select * from flights1 where source like '" + src + "' and destination like '" + des + "'");

                    %>
                    <table border="0" align="center">
                        <tr>
                            <th colspan="4">
                                These are the flights available between <%= request.getParameter("Source") %> and <%= request.getParameter("Destination") %>:
                            </th>
                        </tr>
                        <tr bgcolor="#ffcc66">
                            <th>Airlines</th>
                            <th>Flight No.</th>
                            <th>Departure</th>
                            <th>Arrival</th>
                            <th>Duration(mins)</th>
                            <th>Fare (Rs.)</th>
                            <th>Available Seats</th>
                            <th></th>
                        </tr>
                        <% while (rs1.next()) {%>
                        <tr>
                            <td> <input type="text" name="airlines" value="<%= rs1.getString("AIRLINES")%>" readonly="readonly" size="20"> </td>
                            <td> <input type="text" name="flightno" value="<%= rs1.getString("FLIGHT_NO")%>" readonly="readonly" size="12"> </td>
                            <td> <input type="text" name="deptime" value="<%= rs1.getString("DEPARTURE_TIME")%>" readonly="readonly" size="12"> </td>
                            <td> <input type="text" name="arrtime" value="<%= rs1.getString("ARRIVAL_TIME")%>" readonly="readonly" size="12"> </td>
                            <td> <input type="text" name="dur" value="<%= rs1.getString("DURATION")%>" readonly="readonly" size="12"> </td>
                            <td> <input type="text" name="fare" value="<%= rs1.getString("FARE")%>" readonrly="readonly" size="12"> </td>
                            <%
                                String flightNo = rs1.getString("FLIGHT_NO");
                                rs2 = st2.executeQuery("select count(*) from bookings group by flight_no, DOJ  having flight_no = '" + flightNo + "' and DOJ = '" + date + "'");
                                seatsLeft = 100;
                                while (rs2.next()) {
                                    seatsLeft = 100 - rs2.getInt("count(*)");
                                }
                            %>
                            <td><input type="text" name="seats" value="<%= seatsLeft%>" readonly="readonly" size="20"> </td>
                           <td> <input type="Submit" value="Book"> </td> 
                           
                        </tr>
                        <%}%>

                    </table>
                    </form>
                </td>
            </tr>
        </table>
    </body>
</html>